import logging

import oracledb
from database.database_conf import DatabaseConf
from database.database_object import DatabaseObject


class OracleObject(DatabaseObject):
    def connect(self, db_conf: DatabaseConf) -> bool:
        ret = True
        try:
            dsn = f"{db_conf.db_address}:{db_conf.db_port}/{db_conf.db_name}"
            self.db_url = f"Oracle[{dsn}][{db_conf.db_username}][{db_conf.db_password}]"
            logging.info(self.db_url)
            self.db_con = oracledb.connect(user=db_conf.db_username, password=db_conf.db_password, dsn=dsn)
        except Exception as ex:
            ret = False
            self.last_error = str(ex)
            logging.error(ex)
        return ret

    def get_version(self) -> str:
        sql = "SELECT VALUE AS DB_VERSION FROM nls_database_parameters WHERE parameter='NLS_RDBMS_VERSION'"
        row = self.fetchone(sql)
        if row:
            return row[0]
        return ""

    def get_schema_list(self) -> list:
        schema_list = []
        sql = "SELECT USERNAME AS DB_NAME FROM (SELECT USERNAME, CASE WHEN (USERNAME = USER) THEN 1 ELSE 0 END ISCURRENTUSER FROM SYS.ALL_USERS)T ORDER BY ISCURRENTUSER DESC , USERNAME"
        rows = self.fetchall(sql)
        for row in rows:
            schema_list.append(row[0])
        return schema_list

    def get_table_list(self, schema_name: str) -> list:
        table_list = []
        sql = "SELECT TABLE_NAME FROM SYS.ALL_ALL_TABLES T WHERE T.OWNER = '%s' ORDER BY TABLE_NAME" % schema_name
        rows = self.fetchall(sql)
        for row in rows:
            table_list.append(row[0])
        return table_list

    def get_filed_list(self, schema_name: str, table_name: str) -> list:
        filed_list = []
        sql = "select atc.column_name  as field_name ,atc.data_type as field_type ,atc.data_length as field_length ,acc.comments as field_desc from all_tab_columns atc LEFT JOIN all_col_comments acc on  acc.owner=atc.owner and acc.table_name=atc.table_name and acc.column_name=atc.column_name  where atc.owner='%s' and atc.table_name='%s' ORDER BY atc.column_id"%(schema_name, table_name)
        rows = self.fetchall(sql)
        for row in rows:
            filed_row = dict()
            filed_row["field_name"] = row[0].lower()
            filed_row["field_type"] = row[1].lower()
            filed_row["field_length"] = row[2]
            filed_row["field_desc"] = row[3]

            filed_list.append(filed_row)
        return filed_list
